/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package dao;

import beans.Barrio;
import beans.Boliche;
import beans.Profesion;
import beans.Socio;
import java.util.List;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Restrictions;

/**
 *
 * @author nMoncho
 */
public class ReportingDao {

    public static List getRankingSocios(){
        SessionFactory sf = SimpleSessionFactory.getSessionFactory(null);
        Session session = sf.openSession();
        session.beginTransaction();
        String query = "select socio.legajo,socio.apellido||' '||socio.nombre, count(visitas), sum(visitas.cantidadAmigos), coalesce(sum(visitas.puntajeParcial), 0) "
                        + " from beans.Socio socio left join socio.visitas visitas "
                        + " group by socio.legajo, socio.apellido||' '||socio.nombre "
                        + " order by coalesce(sum(visitas.puntajeParcial), 0) desc";
        
        Query q = session.createQuery(query);


        List result = q.list();

        session.close();

        return result;
    }

    public static List getRankingSociosConFiltros(Boliche boliche, String sexo, String[] razones, Barrio[] barrios, Profesion[] estudios){
        SessionFactory sf = SimpleSessionFactory.getSessionFactory(null);
        Session session = sf.openSession();
        
        String query = "select socio.legajo,socio.apellido||' '||socio.nombre, count(visitas), sum(visitas.cantidadAmigos), coalesce(sum(visitas.puntajeParcial), 0) "
                        + " from beans.Socio socio left join socio.visitas visitas ";

        StringBuilder where = new StringBuilder();
        if(boliche != null)
            where.append(" where socio.boliche.id = ").append(boliche.getId());
        if(sexo != null){
            if(where.length() == 0)
                where.append("where socio.sexo = '").append(sexo).append("'");
            else
                where.append(" and socio.sexo = '").append(sexo).append("'");
        }
        if(razones != null && razones.length > 0){
            if(where.length() == 0)
                where.append("where socio.razonSocio in (");
            else
                where.append(" and socio.razonSocio in (");

            for(int i=0;i<razones.length;i++){
                if(i == razones.length-1)
                    where.append("'").append(razones[i]).append("')");
                else
                    where.append("'").append(razones[i]).append("', ");
            }
        }
        if(where.length() > 0)
            query += where.append(" ").toString();

        query += " group by socio.legajo, socio.apellido||' '||socio.nombre "
                + " order by coalesce(sum(visitas.puntajeParcial), 0) desc";

        Query hQuery = session.createQuery(query);
        List result = hQuery.list();

        session.close();

        return result;
    }

}
